package com.jiuqi.bi.irp.inforesource.manager;

import com.jiuqi.bi.ds.manager.DataSourceManager;
import com.jiuqi.bi.ds.service.bean.DataSource;
import com.jiuqi.bi.irp.core.common.IRPException;
import com.jiuqi.bi.irp.core.datasource.manager.DataSourceConnectionManager;
import com.jiuqi.bi.irp.core.enums.BooleanEnum;
import com.jiuqi.bi.irp.core.enums.DataTypeEnum;
import com.jiuqi.bi.irp.core.item.BaseInfo;
import com.jiuqi.bi.irp.core.item.DBDataStructure;
import com.jiuqi.bi.irp.core.manager.DBDataStructureManager;
import com.jiuqi.bi.irp.inforesource.dao.IResStructureDataManageDao;
import com.jiuqi.bi.util.Guid;
import com.jiuqi.bi.util.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.sql.Connection;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class IResStructureDataManager {

    @Autowired
    private IResStructureDataManageDao iResStructureDataManageDao;

    @Autowired
    private ExcelOptionalManager excelOptionalManager;

    @Autowired
    private IResStructureDataValueVaildator valueVaildator;

    private static final Logger logger = LoggerFactory.getLogger(IResStructureDataManager.class);

    /**
     * 判断 数据库 和  数据结构  中的表结构属性项是否一致
     *
     * @param resGuid 资源guid
     * @return
     */
    public Boolean isDatabaseAndDatastructureSync(String resGuid) throws Exception {
        //从数据库中获取 表结构属性项
        List<DBDataStructure> databaseAttributes = getAttributesFromDataBaseByGuid(resGuid);
        //从数据结构中获取 表结构属性项
        List<DBDataStructure> datastructureAttributes = getAttributesFromDataStructureByGuid(resGuid);

        List<String> databaseAttributeNames = databaseAttributes.stream()
                .map(DBDataStructure::getFieldName)
                .collect(Collectors.toList());
        List<String> datastructureAttributeNames = datastructureAttributes.stream()
                .map(DBDataStructure::getFieldName)
                .collect(Collectors.toList());
        //数据结构属性字段 全部被包括在 数据库属性字段中
        if (!databaseAttributeNames.containsAll(datastructureAttributeNames))
            return false;

        return true;
    }

    public JSONObject addData(String resGuid, List<Map<String, Object>> addDataList) throws Exception {
        try {
            JSONObject addItem = new JSONObject();
            String tableName = getTableNameByGuid(resGuid);

            //从数据结构中获取 表结构属性项
            List<DBDataStructure> datastructureAttributes = getAttributesFromDataStructureByGuid(resGuid);
            Map<String, DBDataStructure> datastructureAttributeDictronary =
                    datastructureAttributes
                            .stream()
                            .collect(Collectors.toMap(
                                    DBDataStructure::getFieldName,
                                    dbDataStructure -> dbDataStructure
                            ));

            //从数据库中获取 表结构属性项
            List<DBDataStructure> databaseAttributes = getAttributesFromDataBaseByGuid(resGuid);
            Map<String, DBDataStructure> databaseAttributeDictronary =
                    databaseAttributes
                            .stream()
                            .collect(Collectors.toMap(
                                    DBDataStructure::getFieldName,
                                    dbDataStructure -> dbDataStructure
                            ));

            List<String> fieldNames = new ArrayList<>();
            Map<String, Object> fieldValueDictronary = new HashMap<>();
            for (Map<String, Object> stringObjectMap : addDataList) {
                String fieldName = (String) stringObjectMap.get("fieldName");
                Object fieldValue = stringObjectMap.get("fieldValue");

                fieldNames.add(fieldName);
                fieldValueDictronary.put(fieldName, fieldValue);
            }

            //数据库 表结构属性校验
            for (Map.Entry<String, Object> entry : fieldValueDictronary.entrySet()) {
                String fieldName = entry.getKey();
                Object fieldValue = entry.getValue();

                DBDataStructure datastructureAttribute = datastructureAttributeDictronary.get(fieldName);
                DBDataStructure databaseAttribute = databaseAttributeDictronary.get(fieldName);

                try {
                    valueVaildator.validNormalValue(fieldValue, datastructureAttribute, databaseAttribute);
                } catch (Exception e) {
                    throw new Exception("【数据库校验】：字段【" + datastructureAttribute.getFieldTitle() + "】异常，" + e.getMessage());
                }

            }

            DataSource dataSource = getDataSourceByGuid(resGuid);
            String dataSourceGuid = dataSource.getGuid();
            try (Connection conn = DataSourceConnectionManager.getConnection(dataSourceGuid)) {
                if (Objects.nonNull(conn)) {
                    iResStructureDataManageDao.addData(conn, tableName, fieldNames, fieldValueDictronary, databaseAttributeDictronary);
                    JSONArray resultArr = iResStructureDataManageDao.normalQuery(conn, tableName, fieldNames, fieldValueDictronary, databaseAttributeDictronary);
                    if (!resultArr.isEmpty()) addItem = (JSONObject) resultArr.get(0);
                } else {
                    logger.error("从资源获取连接信息失败");
                    throw new Exception("从资源获取连接信息失败");
                }
            }
            return addItem;
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new Exception(e.getMessage());
        }
    }

    public JSONObject modifyDataByPK(String resGuid, List<Map<String, Object>> modifyDataByPKParam) throws Exception {
        try {
            JSONObject modifyItem = new JSONObject();
            String tableName = getTableNameByGuid(resGuid);

            int num = modifyDataByPKParam.size();
            List<String> primaryKeyNameList = new ArrayList<>(num);
            List<String> commonFieldNameList = new ArrayList<>(num);
            Map<String, Object> fieldValueDictronary = new HashMap<>();

            //从数据结构中获取 表结构属性项
            List<DBDataStructure> datastructureAttributes = getAttributesFromDataStructureByGuid(resGuid);
            Map<String, DBDataStructure> datastructureAttributeDictronary =
                    datastructureAttributes
                            .stream()
                            .collect(Collectors.toMap(
                                    DBDataStructure::getFieldName,
                                    dbDataStructure -> dbDataStructure
                            ));

            //从数据库中获取 表结构属性项
            List<DBDataStructure> databaseAttributes = getAttributesFromDataBaseByGuid(resGuid);
            Map<String, DBDataStructure> databaseAttributeDictronary =
                    databaseAttributes
                            .stream()
                            .collect(Collectors.toMap(
                                    DBDataStructure::getFieldName,
                                    dbDataStructure -> dbDataStructure
                            ));

            for (Map<String, Object> oneFieldMap : modifyDataByPKParam) {
                String fieldName = (String) oneFieldMap.get("fieldName");

                DBDataStructure databaseFieldStructure = databaseAttributeDictronary.get(fieldName);
                DBDataStructure datastructureFieldStructure =  datastructureAttributeDictronary.get(fieldName);

                boolean databaseIsPrimaryKey = BooleanEnum.YES.id() == databaseFieldStructure.getIsPrimaryKey();
                boolean datastructureIsPrimaryKey = BooleanEnum.YES.id() == datastructureFieldStructure.getIsPrimaryKey();

                if(databaseIsPrimaryKey != datastructureIsPrimaryKey)
                    throw new Exception("字段【" + datastructureFieldStructure.getFieldTitle() + "】数据结构与数据库键属性设置不一致");

                if (databaseIsPrimaryKey)
                    primaryKeyNameList.add(fieldName);
                else
                    commonFieldNameList.add(fieldName);

                Object fieldValue = oneFieldMap.get("fieldValue");
                fieldValueDictronary.put(fieldName, fieldValue);
            }

            //数据库 表结构属性校验
            for (Map.Entry<String, Object> entry : fieldValueDictronary.entrySet()) {
                String fieldName = entry.getKey();
                Object fieldValue = entry.getValue();

                DBDataStructure datastructureAttribute = datastructureAttributeDictronary.get(fieldName);
                DBDataStructure databaseAttribute = databaseAttributeDictronary.get(fieldName);

                try {
                    valueVaildator.validNormalValue(fieldValue, datastructureAttribute, databaseAttribute);
                } catch (Exception e) {
                    throw new Exception("【数据库校验】：字段【" + datastructureAttribute.getFieldTitle() + "】异常，" + e.getMessage());
                }
            }

            DataSource dataSource = getDataSourceByGuid(resGuid);
            String dataSourceGuid = dataSource.getGuid();
            try (Connection conn = DataSourceConnectionManager.getConnection(dataSourceGuid)) {
                if (Objects.nonNull(conn)) {
                    iResStructureDataManageDao.modifyDataByPK(conn, tableName, primaryKeyNameList, commonFieldNameList, fieldValueDictronary, databaseAttributeDictronary);
                    JSONArray resultArr = iResStructureDataManageDao.normalQuery(conn, tableName, primaryKeyNameList, fieldValueDictronary, databaseAttributeDictronary);
                    if (!resultArr.isEmpty()) modifyItem = (JSONObject) resultArr.get(0);
                } else {
                    logger.error("从资源获取连接信息失败");
                    throw new Exception("从资源获取连接信息失败");
                }
            }
            return modifyItem;
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new Exception(e.getMessage());
        }
    }

    public JSONObject modifyDataByAllFields(String resGuid, List<Map<String, Object>> oldDataList, List<Map<String, Object>> newDataList) throws Exception {
        try {
            JSONObject modifyItem = new JSONObject();
            String tableName = getTableNameByGuid(resGuid);

            int num = oldDataList.size();
            List<String> oldFieldNameList = new ArrayList<>(num);
            List<String> newFieldNameList = new ArrayList<>();
            Map<String, Object> oldFieldValueDictronary = new HashMap<>();
            Map<String, Object> newFieldValueDictronary = new HashMap<>();

            //从数据结构中获取 表结构属性项
            List<DBDataStructure> datastructureAttributes = getAttributesFromDataStructureByGuid(resGuid);
            Map<String, DBDataStructure> datastructureAttributeDictronary =
                    datastructureAttributes
                            .stream()
                            .collect(Collectors.toMap(
                                    DBDataStructure::getFieldName,
                                    dbDataStructure -> dbDataStructure
                            ));

            //从数据库中获取 表结构属性项
            List<DBDataStructure> databaseAttributes = getAttributesFromDataBaseByGuid(resGuid);
            Map<String, DBDataStructure> databaseAttributeDictronary =
                    databaseAttributes
                            .stream()
                            .collect(Collectors.toMap(
                                    DBDataStructure::getFieldName,
                                    dbDataStructure -> dbDataStructure
                            ));

            for (Map<String, Object> oneFieldMap : oldDataList) {
                String fieldName = (String) oneFieldMap.get("fieldName");
                Object fieldValue = oneFieldMap.get("fieldValue");

                oldFieldNameList.add(fieldName);
                oldFieldValueDictronary.put(fieldName, fieldValue);
            }

            for (Map<String, Object> oneFieldMap : newDataList) {
                String fieldName = (String) oneFieldMap.get("fieldName");
                Object fieldValue = oneFieldMap.get("fieldValue");

                newFieldNameList.add(fieldName);
                newFieldValueDictronary.put(fieldName, fieldValue);
            }


            //数据库 表结构属性校验
            for (Map.Entry<String, Object> entry : newFieldValueDictronary.entrySet()) {
                String fieldName = entry.getKey();
                Object fieldValue = entry.getValue();

                DBDataStructure datastructureAttribute = datastructureAttributeDictronary.get(fieldName);
                DBDataStructure databaseAttribute = databaseAttributeDictronary.get(fieldName);

                try {
                    valueVaildator.validNormalValue(fieldValue, datastructureAttribute, databaseAttribute);
                } catch (Exception e) {
                    throw new Exception("【数据库校验】：字段【" + datastructureAttribute.getFieldTitle() + "】异常，" + e.getMessage());
                }
            }


            DataSource dataSource = getDataSourceByGuid(resGuid);
            String dataSourceGuid = dataSource.getGuid();
            try (Connection conn = DataSourceConnectionManager.getConnection(dataSourceGuid)) {
                if (Objects.nonNull(conn)) {
                    iResStructureDataManageDao.modifyDataByAllFields(conn, tableName, oldFieldNameList, newFieldNameList, oldFieldValueDictronary, newFieldValueDictronary, databaseAttributeDictronary);
                    JSONArray resultArr = iResStructureDataManageDao.normalQuery(conn, tableName, newFieldNameList, newFieldValueDictronary, databaseAttributeDictronary);
                    if (!resultArr.isEmpty()) modifyItem = (JSONObject) resultArr.get(0);
                } else {
                    logger.error("从资源获取连接信息失败");
                    throw new Exception("从资源获取连接信息失败");
                }
            }
            return modifyItem;
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new Exception(e.getMessage());
        }
    }

    public void deleteDataByPK(String resGuid, List<String> tablePKNameList, List<List<Object>> deleteParamList) throws Exception {
        try {

            String tableName = getTableNameByGuid(resGuid);

            //从数据结构中获取 表结构属性项
            List<DBDataStructure> datastructureAttributes = getAttributesFromDataStructureByGuid(resGuid);
            Map<String, DBDataStructure> datastructureAttributeDictronary =
                    datastructureAttributes
                            .stream()
                            .collect(Collectors.toMap(
                                    DBDataStructure::getFieldName,
                                    dbDataStructure -> dbDataStructure
                            ));

            //从数据库中获取 表结构属性项
            List<DBDataStructure> databaseAttributes = getAttributesFromDataBaseByGuid(resGuid);
            Map<String, DBDataStructure> databaseAttributeDictronary =
                    databaseAttributes
                            .stream()
                            .collect(Collectors.toMap(
                                    DBDataStructure::getFieldName,
                                    dbDataStructure -> dbDataStructure
                            ));

            for (int i = 0; i <= tablePKNameList.size() - 1; i++) {
                String pkName = tablePKNameList.get(i);
                DBDataStructure databaseFieldStructure = databaseAttributeDictronary.get(pkName);
                DBDataStructure datastructureFieldStructure =  datastructureAttributeDictronary.get(pkName);

                boolean databaseIsPrimaryKey = BooleanEnum.YES.id() == databaseFieldStructure.getIsPrimaryKey();
                boolean datastructureIsPrimaryKey = BooleanEnum.YES.id() == datastructureFieldStructure.getIsPrimaryKey();

                if(databaseIsPrimaryKey != datastructureIsPrimaryKey)
                    throw new Exception("字段【" + datastructureFieldStructure.getFieldTitle() + "】数据结构与数据库键属性设置不一致");
            }

            DataSource dataSource = getDataSourceByGuid(resGuid);
            String dataSourceGuid = dataSource.getGuid();
            try (Connection conn = DataSourceConnectionManager.getConnection(dataSourceGuid)) {
                if (Objects.nonNull(conn))
                    iResStructureDataManageDao.deleteDataByPK(conn, tableName, tablePKNameList, deleteParamList, databaseAttributeDictronary);
                else {
                    logger.error("从资源获取连接信息失败");
                    throw new Exception("从资源获取连接信息失败");
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new Exception(e.getMessage());
        }
    }

    public void deleteDataByAllFields(String resGuid, List<Map<String, Object>> allFieldsInfo) throws Exception {
        try {

            String tableName = getTableNameByGuid(resGuid);

            List<DBDataStructure> fieldAttributes = getAttributesFromDataBaseByGuid(resGuid);
            Map<String, DBDataStructure> fieldAttributeDictronary =
                    fieldAttributes
                            .stream()
                            .collect(Collectors.toMap(
                                    DBDataStructure::getFieldName,
                                    dbDataStructure -> dbDataStructure
                            ));

            List<Map<String, Object>> dataBaseExistRows = new ArrayList<>();
            for (Map<String, Object> oneFieldMap : allFieldsInfo) {
                Map<String, Object> dataBaseExistOneRowFields = new HashMap<>();
                for (Map.Entry<String, Object> entry : oneFieldMap.entrySet()) {
                    String fieldName = entry.getKey();
                    Object fieldValue = entry.getValue();
                    dataBaseExistOneRowFields.put(fieldName, fieldValue);
                }
                dataBaseExistRows.add(dataBaseExistOneRowFields);
            }

            DataSource dataSource = getDataSourceByGuid(resGuid);
            String dataSourceGuid = dataSource.getGuid();
            try (Connection conn = DataSourceConnectionManager.getConnection(dataSourceGuid)) {
                if (Objects.nonNull(conn))
                    iResStructureDataManageDao.deleteDataByAllFields(conn, tableName, dataBaseExistRows, fieldAttributeDictronary);
                else {
                    logger.error("从资源获取连接信息失败");
                    throw new Exception("从资源获取连接信息失败");
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new Exception(e.getMessage());
        }
    }

    public void clearAllDataByGuid(String resGuid) throws Exception {
        DataSource dataSource = getDataSourceByGuid(resGuid);
        String dataSourceGuid = dataSource.getGuid();
        try (Connection conn = DataSourceConnectionManager.getConnection(dataSourceGuid)) {
            String tableName = getTableNameByGuid(resGuid);
            iResStructureDataManageDao.clearAllDataByGuid(conn, tableName);
        }
    }

    public JSONObject queryByFilter(String resGuid, String currentPage, String pageSize, List<Map<String, Object>> conditions) {
        JSONObject result = new JSONObject();
        boolean isSuccess = true;
        try {
            DataSource dataSource = getDataSourceByGuid(resGuid);
            String dataSourceGuid = dataSource.getGuid();
            try (Connection conn = DataSourceConnectionManager.getConnection(dataSourceGuid)) {
                if (Objects.isNull(conn)) {
                    logger.error("从资源获取连接信息失败");
                    throw new Exception("从资源获取连接信息失败");
                }

                String tableName = getTableNameByGuid(resGuid);

                //从数据库中获取 表结构属性项
                List<DBDataStructure> structures = getAttributesFromDataBaseByGuid(resGuid);

                int num = conditions.size();
                List<String> fieldNames = new ArrayList<>(num);
                Map<String, Object> fieldValueDictronary = new HashMap<>(num);
                for (Map<String, Object> stringObjectMap : conditions) {
                    String fieldName = (String) stringObjectMap.get("name");
                    Object fieldValue = stringObjectMap.get("value");

                    fieldNames.add(fieldName);
                    fieldValueDictronary.put(fieldName, fieldValue);
                }

                JSONArray filtedAndPagedDatas = iResStructureDataManageDao.queryByFilterAndPage(conn, pageSize, currentPage, tableName, fieldNames, fieldValueDictronary, structures);
                int totalSize = iResStructureDataManageDao.fuzzyQueryTotalSize(conn, tableName, fieldNames, fieldValueDictronary);

                result.put("totalSize", totalSize);
                result.put("data", filtedAndPagedDatas);
            }

        } catch (Exception e) {
            isSuccess = false;
            result.put("errorInfo", e.getMessage());
        } finally {
            result.put("processSuccess", isSuccess);
        }
        return result;
    }

    public void importDbStructSurfaceData(String resGuid, MultipartFile fileItem) throws Exception {
        //校验guid
        validResGuid(resGuid);

        //校验文件是否为空
        if (fileItem.isEmpty()) {
            logger.error("导入文件不能为空");
            throw new IRPException("导入文件不能为空");
        }

        try (InputStream inStream = fileItem.getInputStream()) {
            Workbook workbook = null;

            //校验文件类型
            String fileName = fileItem.getOriginalFilename();
            int point = fileName.lastIndexOf(".");
            String postfix = StringUtils.substring(fileName, point);
            if (".xls".equalsIgnoreCase(postfix)) {
                workbook = new HSSFWorkbook(inStream);
            } else if (".xlsx".equalsIgnoreCase(postfix)) {
                workbook = new XSSFWorkbook(inStream);
            } else {
                logger.error("请选择扩展类型为*.xls,*.xlsx的文件");
                throw new IRPException("请选择扩展类型为*.xls,*.xlsx的文件");
            }

            //从数据结构中获取 表结构属性项
            List<DBDataStructure> datastructureAttributes = getAttributesFromDataStructureByGuid(resGuid);
            Map<String, DBDataStructure> datastructureAttributeDictronary =
                    datastructureAttributes
                            .stream()
                            .collect(Collectors.toMap(
                                    DBDataStructure::getFieldName,
                                    dbDataStructure -> dbDataStructure
                            ));
            //从数据库中获取 表结构属性项
            List<DBDataStructure> databaseAttributes = getAttributesFromDataBaseByGuid(resGuid);
            Map<String, DBDataStructure> databaseAttributeDictronary =
                    databaseAttributes
                            .stream()
                            .collect(Collectors.toMap(
                                    DBDataStructure::getFieldName,
                                    dbDataStructure -> dbDataStructure
                            ));

            //读取文件内容
            JSONArray fileDatas = excelOptionalManager.importExcelData(workbook, datastructureAttributeDictronary, databaseAttributeDictronary);


            //获取表名
            String tableName = getTableNameByGuid(resGuid);

            //保存文件内容
            DataSource dataSource = getDataSourceByGuid(resGuid);
            String dataSourceGuid = dataSource.getGuid();
            Connection conn = DataSourceConnectionManager.getConnection(dataSourceGuid);
            try {
                conn.setAutoCommit(false);
                iResStructureDataManageDao.clearAllDataByGuid(conn, tableName);
                iResStructureDataManageDao.saveImportFile(conn, tableName, databaseAttributes, fileDatas);
                conn.commit();
            } catch (Exception e) {
                conn.rollback();
                logger.error(e.getMessage());
                throw new IRPException(e.getMessage());
            } finally {
                if (conn != null) {
                    conn.setAutoCommit(true);
                    conn.close();
                }
            }
        }
    }

    public void exportDbStructSurfaceData(String resGuid, List<Map<String, Object>> conditions, HttpServletResponse response) throws Exception {
        validResGuid(resGuid);

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);

        //查询出物理表名
        String tableName = getTableNameByGuid(resGuid);

        //从数据库中获取 表结构属性项
        List<DBDataStructure> databaseAttributes = getAttributesFromDataBaseByGuid(resGuid);

        //从数据结构中获取 表结构属性项
        List<DBDataStructure> datastructureAttributes = getAttributesFromDataStructureByGuid(resGuid);

        List<String> hearders = new ArrayList<>();
        for (DBDataStructure struct : datastructureAttributes) {
            hearders.add(struct.getFieldTitle() + "[" + struct.getFieldName() + "]");
        }
        String[] titleText = new String[hearders.size()];
        hearders.toArray(titleText);

        //查询物理表下的数据
        DataSource dataSource = getDataSourceByGuid(resGuid);
        String dataSourceGuid = dataSource.getGuid();
        try (Connection conn = DataSourceConnectionManager.getConnection(dataSourceGuid)) {

            int num = conditions.size();
            List<String> fieldNames = new ArrayList<>(num);
            Map<String, Object> fieldValueDictronary = new HashMap<>(num);
            for (Map<String, Object> stringObjectMap : conditions) {
                String fieldName = (String) stringObjectMap.get("name");
                Object fieldValue = stringObjectMap.get("value");

                fieldNames.add(fieldName);
                fieldValueDictronary.put(fieldName, fieldValue);
            }
            JSONArray jsonArray = iResStructureDataManageDao.queryByFilter(conn, tableName, fieldNames, fieldValueDictronary, databaseAttributes);

            XSSFSheet sheet = excelOptionalManager.createSheet(workbook, 0, titleText);
            sheet.setDefaultColumnWidth(15);

            String[] cellText = new String[titleText.length];

            for (int i = 0; i < jsonArray.length(); i++) {
                JSONObject jsonObject = jsonArray.getJSONObject(i);

                for (int j = 0; j < datastructureAttributes.size(); j++) {
                    String fieldName = datastructureAttributes.get(j).getFieldName();
                    String value = jsonObject.optString(fieldName);
                    if (datastructureAttributes.get(j).getDataType() == DataTypeEnum.DATE.id()) { //格式化时期类型
                        value = valueVaildator.formatDateTime("yyyy-MM-dd", value);
                    }
                    cellText[j] = value;
                }
                excelOptionalManager.createRow(sheet, cellStyle, cellText);
            }

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-disposition", "attachment");

            try (ServletOutputStream stream = response.getOutputStream()) {
                workbook.write(stream);
                stream.flush();
                workbook.close();
            }

        }
    }

    /**
     * 后端共用校验
     *
     * @param resGuid
     * @param primaryKeyFieldList
     */
    public Boolean validPrimaryKey(String resGuid, List<Map<String, Object>> primaryKeyFieldList) throws Exception {
        int resultNum = 0;
        String tableName = getTableNameByGuid(resGuid);
        List<DBDataStructure> fieldAttributes = getAttributesFromDataBaseByGuid(resGuid);

        int num = primaryKeyFieldList.size();
        List<String> fieldNames = new ArrayList<>(num);
        Map<String, Object> fieldValueDictronary = new HashMap<>(num);
        for (Map<String, Object> stringObjectMap : primaryKeyFieldList) {
            String fieldName = (String) stringObjectMap.get("fieldName");
            Object fieldValue = stringObjectMap.get("fieldValue");

            fieldNames.add(fieldName);
            fieldValueDictronary.put(fieldName, fieldValue);
        }

        Map<String, DBDataStructure> fieldAttributeDictronary =
                fieldAttributes
                        .stream()
                        .collect(Collectors.toMap(
                                DBDataStructure::getFieldName,
                                dbDataStructure -> dbDataStructure
                        ));

        DataSource dataSource = getDataSourceByGuid(resGuid);
        String dataSourceGuid = dataSource.getGuid();
        try (Connection conn = DataSourceConnectionManager.getConnection(dataSourceGuid)) {
            resultNum = iResStructureDataManageDao.normalQuery(conn, tableName, fieldNames, fieldValueDictronary, fieldAttributeDictronary).length();
        }
        return resultNum == 0;
    }

    //=====================================             辅助方法             ============================================

    /**
     * 从数据库中获取 表结构属性项
     *
     * @param resGuid 资源GUID
     * @return 表结构属性项
     * @throws Exception
     */
    private List<DBDataStructure> getAttributesFromDataBaseByGuid(String resGuid) throws Exception {
        String tableName = getTableNameByGuid(resGuid);
        DBDataStructureManager dbDataStructureManager = new DBDataStructureManager();
        return dbDataStructureManager.getLogicFields(tableName, resGuid);

    }

    /**
     * 从数据结构中获取 表结构属性项
     *
     * @param resGuid 资源GUID
     * @return 表结构属性项
     * @throws Exception
     */
    public List<DBDataStructure> getAttributesFromDataStructureByGuid(String resGuid) throws Exception {
        validResGuid(resGuid);

        DBDataStructureManager dbDataStructureManager = new DBDataStructureManager();
        return dbDataStructureManager.getByDrGuid(resGuid);
    }

    /**
     * 获取 资源 对应数据库表名
     *
     * @param resGuid 资源guid
     * @return
     * @throws Exception
     */
    public String getTableNameByGuid(String resGuid) throws Exception {
        BaseInfo baseInfo = getBaseInfoByGuid(resGuid);
        JSONObject dsCfgJSON = new JSONObject(baseInfo.getDataSourceCfg());
        return dsCfgJSON.optString("tableName");
    }

    /**
     * 获取 数据源   （信息资源中 包括 数据源）
     *
     * @param resGuid 资源guid
     * @return
     * @throws Exception
     */
    private DataSource getDataSourceByGuid(String resGuid) throws Exception {
        BaseInfo baseInfo = getBaseInfoByGuid(resGuid);
        String sourceGuid = baseInfo.getDataSource();
        DataSourceManager dsManager = new DataSourceManager();
        DataSource dataSource = dsManager.getDataSourceByID(sourceGuid);
        if (dataSource == null) {
            logger.error("未找到指定记录");
            throw new Exception("未找到指定记录");
        }
        return dataSource;
    }

    /**
     * 获取 信息资源
     *
     * @param resGuid 资源guid
     * @return
     * @throws Exception
     */
    public BaseInfo getBaseInfoByGuid(String resGuid) throws Exception {
        validResGuid(resGuid);

        // 获取原有表字段
        InfoResManager infoResManager = new InfoResManager();
        BaseInfo baseInfo = infoResManager.getBaseInfoByGuid(resGuid);
        if (baseInfo == null) {
            logger.error("指定guid[" + resGuid + "]的信息资源不存在！");
            throw new Exception("指定guid[" + resGuid + "]的信息资源不存在！");
        }
        return baseInfo;
    }

    /**
     * 校验 resGuid
     *
     * @throws IRPException
     */
    private void validResGuid(String resGuid) throws IRPException {
        if (StringUtils.isEmpty(resGuid)) {
            logger.error("资源节点不能为空");
            throw new IRPException("资源节点不能为空");
        }

        if (StringUtils.isNotEmpty(resGuid) && !Guid.isGuid(resGuid)) {
            logger.error("资源节点GUID非法");
            throw new IRPException("资源节点GUID非法");
        }
    }
}
